from datetime import datetime
import time

import pytz
from django.db import connection

from app1.models import Student


def read_records():
    f = open('students.txt')
    names = ('name', 'gender', 'age', 'birth', 'cellphone')
    records = []
    for line in f:
        elements = line.strip().split(',')
        record = dict(zip(names, elements))
        records.append(record)
    return records


# 昨天测试耗时31秒
def add_api(records):
    tz = pytz.timezone('Asia/Chongqing')
    for r in records:
        t = time.strptime(r['birth'], '%Y-%m-%d %H:%M:%S')
        birth = datetime(*t[:6], tzinfo=tz)
        book = Student(name=r['name'], gender=int(r['gender']),
                       age=int(r['age']), birth=birth,
                       cellphone=r['cellphone'])
        book.save()


def add_raw(conn, records):
    cur = conn.cursor()
    for r in records:
        sql = ('insert into app1_student (name, gender, age, birth,'
               'cellphone) values(%s, %s, %s, %s, %s)')
        cur.execute(sql, (r['name'], int(r['gender']),
                          int(r['age']), r['birth'], r['cellphone']))
    conn.commit()
    conn.close()


def add_raw2(conn, records):
    cur = conn.cursor()
    # 每次插入N条
    n = 5000
    while True:
        subset = records[:n]
        if not subset:
            break
        records = records[n:]
        sql = 'insert into app1_student (name, gender, age, birth, cellphone) values'
        fmts = []
        vals = []
        for r in subset:
            fmts.append('(%s, %s, %s, %s, %s)')
            vals.extend([r['name'], int(r['gender']), int(r['age']), r['birth'], r['cellphone']])
        fmt_text = ','.join(fmts)
        sql = sql + fmt_text
        vals = tuple(vals)
        cur.execute(sql, vals)
    conn.commit()
    conn.close()


if __name__ == '__main__':
    records = read_records()
    start = time.time()
    # add_api(records)
    # add_raw(connection, records)
    add_raw2(connection, records)
    end = time.time()
    print(end-start)
